package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.ISignUpChargeDao;
import com.eling.elcms.travel.model.PlanItemGroup.SignUpStatus;
import com.eling.elcms.travel.model.SignUpCharge;
import com.eling.elcms.travel.model.view.SignUpChargeView;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class SignUpChargeDaoHibernate extends
		GenericDaoHibernate<SignUpCharge, Long> implements ISignUpChargeDao {

	public SignUpChargeDaoHibernate() {
		super(SignUpCharge.class);
	}

	@SuppressWarnings({ "unchecked" })
	@Override
	public List<SignUpChargeView> queryPlanItem(Long pkServicePoint,
			Long pkDestination, Date startDate, Date startDateEnd,
			String planStatus) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" 	*");
		sql.append("   FROM");
		sql.append(" 	(SELECT DISTINCT ");
		sql.append(" 		tpi.pkPlanItem,tp.`year`,td.pkDestination,td.`name`,tpi.startDate,tpi.endDate,");
		sql.append(" 		COUNT(tsr.pkSignUpRegister) AS signUpNumber,COUNT(travel_sc.pkSignUpCharge) AS chargeNumber,");
		sql.append(" 		tpg.signUpStatus AS `status`,osp.pkServicePoint,tp.planTitle,tpg.pkPlanItemGroup,tpg.isAdjustment");
		sql.append(" 	   FROM travel_planitem tpi");
		sql.append(" 	   LEFT JOIN travel_planitemgroup tpg ON tpg.pkPlanItem = tpi.pkPlanItem");
		sql.append(" 	   LEFT JOIN travel_signupregister tsr ON tsr.pkPlanItemGroup = tpg.pkPlanItemGroup");
		sql.append(" 	   LEFT JOIN ");
		sql.append(" 		(SELECT");
		sql.append(" 			*");
		sql.append(" 		   FROM travel_signupcharge");
		sql.append(" 		   WHERE chargeDate IS NOT NULL");
		sql.append(" 		) travel_sc ON travel_sc.pkSignUpRegister = tsr.pkSignUpRegister");
		sql.append(" 		LEFT JOIN om_servicepoint osp ON osp.pkServicePoint = tpg.pkServicePoint");
		sql.append(" 		LEFT JOIN travel_destination td ON td.pkDestination = tpi.pkDestination");
		sql.append(" 		LEFT JOIN travel_plan_planitem tppi ON tppi.pkPlanItem = tpi.pkPlanItem");
		sql.append(" 		LEFT JOIN travel_plan tp ON tp.pkPlan = tppi.pkPlan");
		sql.append(" 		WHERE osp.pkServicePoint=:pkServicePoint");
		sql.append(" 		AND (tsr.exitGroup = false OR tsr.exitGroup IS NULL) ");
		sql.append(" 		AND tpg.alliance = false ");
		sql.append(" 		GROUP BY tpi.pkPlanItem,tpg.pkPlanItemGroup,tp.pkPlan ");
		sql.append(" 	)tt");
		sql.append("   WHERE tt.startDate>=:startDate");
		sql.append("   AND tt.startDate<=:startDateEnd");
		sql.append("   AND tt.`status` IS NOT NULL");
		if (planStatus.equals("Charge")) { // 收费中
			sql.append("   AND tt.signUpNumber > chargeNumber AND tt.isAdjustment = false ");
		} else if (planStatus.equals("ChargeComplete")) { // 收费完成
			sql.append("   AND tt.signUpNumber = chargeNumber");
			sql.append("   AND tt.`status` != '" + SignUpStatus.AdjustmentReview + "'");
			sql.append("   AND (tt.`status` = '" + SignUpStatus.Confirm + "'  or tt.isAdjustment = true) ");
		} else if (planStatus.equals("Review")) { // 已复核
			sql.append("   AND tt.`status` != '" + SignUpStatus.Confirm + "'  AND tt.isAdjustment = false ");
		} else if(planStatus.equals("Adjustment")){//调整
			sql.append("   AND tt.signUpNumber > chargeNumber AND tt.isAdjustment = true ");
		}else if(planStatus.equals("AdjustmentReview")){//调整复核
			sql.append("   AND tt.`status` = '" + SignUpStatus.AdjustmentReview + "' ");
		}
		if (null != pkDestination) {
			sql.append("   AND tt.pkDestination=:pkDestination");
		}
		List<Object[]> objList = new ArrayList<Object[]>();
		if (null != pkDestination) {
			objList = getSession().createNativeQuery(sql.toString())
					.setParameter("pkServicePoint", pkServicePoint)
					.setParameter("startDate", startDate)
					.setParameter("startDateEnd", startDateEnd)
					.setParameter("pkDestination", pkDestination).list();
		} else {
			objList = getSession().createNativeQuery(sql.toString())
					.setParameter("pkServicePoint", pkServicePoint)
					.setParameter("startDate", startDate)
					.setParameter("startDateEnd", startDateEnd).list();
		}
		List<SignUpChargeView> returnList = new ArrayList<SignUpChargeView>();
		if (!objList.isEmpty()) {
			returnList = returnList(objList);
		}
		return returnList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SignUpChargeView> queryPlanItemByPk(Long pkServicePoint,
			Long pkPlanItem) {
		StringBuffer sql = new StringBuffer();
		List<Object[]> objList;
		sql.append(" SELECT");
		sql.append(" 	*");
		sql.append("   FROM");
		sql.append(" 	(SELECT");
		sql.append(" 		tpi.pkPlanItem,tp.`year`,td.pkDestination,td.`name`,tpi.startDate,tpi.endDate,");
		sql.append(" 		COUNT(tsr.pkSignUpRegister) AS signUpNumber,COUNT(travel_sc.pkSignUpCharge) AS chargeNumber,");
		sql.append(" 		tpg.signUpStatus AS `status`,osp.pkServicePoint,tp.planTitle,tpg.pkPlanItemGroup,tpg.isAdjustment");
		sql.append(" 	   FROM travel_planitem tpi");
		sql.append(" 	   LEFT JOIN travel_planitemgroup tpg ON tpg.pkPlanItem = tpi.pkPlanItem");
		sql.append(" 	   LEFT JOIN travel_signupregister tsr ON tsr.pkPlanItemGroup = tpg.pkPlanItemGroup");
		sql.append(" 	   LEFT JOIN ");
		sql.append(" 		(SELECT");
		sql.append(" 			*");
		sql.append(" 		   FROM travel_signupcharge");
		sql.append(" 		   WHERE chargeDate IS NOT NULL");
		sql.append(" 		) travel_sc ON travel_sc.pkSignUpRegister = tsr.pkSignUpRegister");
		sql.append(" 		LEFT JOIN om_servicepoint osp ON osp.pkServicePoint = tpg.pkServicePoint");
		sql.append(" 		LEFT JOIN travel_destination td ON td.pkDestination = tpi.pkDestination");
		sql.append(" 		LEFT JOIN travel_plan_planitem tppi ON tppi.pkPlanItem = tpi.pkPlanItem");
		sql.append(" 		LEFT JOIN travel_plan tp ON tp.pkPlan = tppi.pkPlan");
		sql.append(" 		WHERE osp.pkServicePoint=:pkServicePoint");
		sql.append(" 		GROUP BY tpi.pkPlanItem");
		sql.append(" 	)tt");
		if(pkPlanItem!=null){
			sql.append("   WHERE tt.pkPlanItem=:pkPlanItem");
			objList = getSession().createNativeQuery(sql.toString())
					.setParameter("pkServicePoint", pkServicePoint)
					.setParameter("pkPlanItem", pkPlanItem).list();
		}else {
			objList = getSession().createNativeQuery(sql.toString())
					.setParameter("pkServicePoint", pkServicePoint).list();
		}
		List<SignUpChargeView> returnList = new ArrayList<SignUpChargeView>();
		if (!objList.isEmpty()) {
			returnList = returnList(objList);
		}
		return returnList;
	}

	public List<SignUpChargeView> returnList(List<Object[]> objList) {
		List<SignUpChargeView> returnList = new ArrayList<SignUpChargeView>();
		for (int i = 0; i < objList.size(); i++) {
			Long pkPlanItem = Long.valueOf(objList.get(i)[0].toString());
			String planName = objList.get(i)[10] == null ? ""
					: objList.get(i)[10].toString();
			Date year = (Date) objList.get(i)[1];
			Long pkDes = objList.get(i)[2] == null ? null :Long.valueOf(objList.get(i)[2].toString());
			String desName = objList.get(i)[2] == null ? null : objList.get(i)[3].toString();
			Date startDates = (Date) objList.get(i)[4];
			Date endDate = (Date) objList.get(i)[5];
			String signUpNumber = objList.get(i)[6] == null ? "" : objList
					.get(i)[6].toString();
			String chargeNumber = objList.get(i)[7] == null ? "" : objList
					.get(i)[7].toString();
			String status = objList.get(i)[8] == null ? "" : objList.get(i)[8]
					.toString();
			Long pkServicePoints = Long.valueOf(objList.get(i)[9].toString());
			Long pkPlanItemGroup = Long.valueOf(objList.get(i)[11].toString());
			Boolean isAdjustment = false;
			if(objList.get(i)[12] != null){
				isAdjustment = (Boolean)objList.get(i)[12] == true ? true : false;
			}
			SignUpChargeView sucv = new SignUpChargeView(pkPlanItem, planName,
					year, pkDes, desName, startDates, endDate, signUpNumber,
					chargeNumber, status, pkServicePoints, pkPlanItemGroup,isAdjustment);
			returnList.add(sucv);
		}
		return returnList;
	}
}
